var express = require('express')
var router = express.Router()
const mysql = require('mysql')
const dbConfig = require('../../dbs/config') // 导入数据库配置

// 定义连接池
const pool = mysql.createPool(dbConfig)

// sql语法
let allSql = 'SELECT * FROM test' //查询所有数据在test中
let oneSql = 'SELECT age FROM test WHERE name=?' // 查询对应名字的年龄
let addSql = 'INSERT INTO test(name, age) VALUES(?,?)' // 增加数据
let delSql = 'DELETE FROM test WHERE name=?' // 删除数据
let updateSql = 'UPDATE test SET age=? WHERE name=?' // 修改数据

router.get('/', function(req, res ,next) {
  // res.type('html')
  res.render('index2')
})


// 接收Json数据接口
let subJson = 'INSERT INTO test2(data) VALUES(?)'
router.post('/subJson', (req, res) => {
  // 获取前台发送过来的数据
  let subReqValue = req.body.data
  // 使用连接池连接数据库
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      // query(sql语句，[值] ,回调函数(错误信息，返回值))
      connection.query(subJson, subReqValue, (err, result) => {
        if(err){
          res.send({
            code: -1,
            msg: '提交失败'
          })
          // 释放连接池的连接
          connection.release()
        }else{
          res.send({
            code: 0,
            msg: '提交成功'
          })
          // 释放连接池的连接
          connection.release()
        }
      })
    }
  })
})

// 测试获取数据库的数据，并返回json给前台
let GetJson = 'SELECT data FROM test2 WHERE id=?'
router.post('/GetJson', (req, res) => {
  let GetReqValue = [req.body.id]
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(GetJson, GetReqValue, (err, result) => {
        if(err){
          console.log(err)
          res.send({
            code: -1,
            msg: '提交失败'
          })
          connection.release()
        }else{
          //将字符串转成json返回前台
          let rs = JSON.parse(result[0].data)
          res.send({
            code: 0,
            msg: '提交成功',
            data: rs
          })
          connection.release()
        }
      })
    }
  })
})

// 查全部数据
router.get('/getAll', (req, res, next) => {
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(allSql, (err, result) => {
        if(err) {
          res.send({
            code: -1,
            msg:'查询失败'
          })
          connection.release()
        }else {
          console.log(result)
          res.send({
            code: 0,
            msg: '查询成功',
            data: result
          })
          connection.release()
        }
      })
    }
  })

});
// 根据name查age
router.post('/searchOne', (req, res, next) => {
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(oneSql, req.body.name, (err, result) => {
        if(err) {
          res.send({
            code: -1,
            msg:'失败'
          })
          connection.release()
        }else {
          console.log(result)
          res.send({
            code: 0,
            msg: '成功',  
            data: result
          })
          connection.release()
        }
      })
    }
  })
});
// 增加数据
router.post('/add', (req, res) =>{
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(addSql, [req.body.name, req.body.age], (err, result) => {
        if(err) {
          res.send({
            code: -1,
            msg: '失败'
          })
          connection.release()
        } else {
          res.send({
            code: 0,
            msg: '增加成功'
          })
          connection.release()
        }
      })
    }
  })
})
// 删除数据
router.post('/del', (req, res) =>{
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(delSql, req.body.name, (err, result) => {
        if(err) {
          res.send({
            code: -1,
            msg: '删除失败'
          })
          connection.release()
        } else {
          res.send({
            code: 0,
            msg: '删除成功'
          })
          connection.release()
        }
      })
    }
  })
})
// 改变数据
router.post('/update', (req, res) =>{
  pool.getConnection((err, connection) => {
    if(err){
      console.log('数据库连接失败')
    }else{
      connection.query(updateSql, [req.body.age, req.body.name], (err, result) => {
        if(err) {
          res.send({
            code: -1,
            msg: '更改失败'
          })
          connection.release()
        } else {
          res.send({
            code: 0,
            msg: '更改成功'
          })
          connection.release()
        }
      })
    }
  })
})
module.exports = router;
